#encoding "utf-8"
=begin

Usage:  this script create new stores
Syntax: ruby create-store.rb id, storeName, ownerPhone, ownerName [parallel_type]
        id must > 1
Example ruby create-store.rb 2 测试小店 13600060044 董学锋

=end

load 'rds_api.rb'

#@rds_mode = 'debug'
@rds = @rds_tt

#准备用节气做随访代号
=begin
立春梅花分外艳，雨水红杏花开鲜；
惊蛰芦林闻雷报，春分蝴蝶舞花间。
清明风筝放断线，谷雨嫩茶翡翠连，
立夏桑果象樱桃，小满养蚕又种田。
芒种育秧放庭前，夏至稻花如白练；
小暑风催早豆熟，大暑池畔赏红莲。
立秋知了催人眠，处暑葵花笑开颜；
白露燕归又来雁，秋分丹桂香满园。
寒露菜苗田间绿，霜降芦花飘满天；
立冬报喜献三瑞，小雪鹅毛片片飞。
大雪寒梅迎风狂，冬至瑞雪兆丰年；
小寒游子思乡归，大寒岁底庆团圆。
=end

def clear_follow_up
    queryRds 'delete from tutem_mall.eb_follow_up where 1 = 1';
    queryRds 'ALTER TABLE tutem_mall.eb_follow_up AUTO_INCREMENT=1';
end

def initiate_dashu
    puts "new survey"

    sql = 'select o.uid as uid, o.real_name as real_name, count(o.id) as sold, u.nickname as nickname from tutem_mall.eb_store_order o left join tutem_mall.eb_wechat_user u on o.uid = u.uid where o.paid = 1 group by u.uid';
    res = queryRds sql
    uids = []
    names = {}
    solds = {}
    res.each do |r| 
        uids += [r['uid']]
        names.store r['uid'], r['nickname']
        names.store r['uid'], r['real_name'] if r['nickname'] == '微信用户'
        solds.store r['uid'], r['sold']
    end

    sqls = []
    uids.each do |uid|

        template_id = 1;
        short_name = names[uid][0..16] #name只有64byte 
        name = short_name + '的产品随访问卷（2021H1）'
        status = 0
        create_time = Time.now.to_i
        submit_time = 0

        #这个content要配合template_id对应的模板一起使用
        content = "尊敬的#{names[uid]}：您在【2021年7月22日之前】在小程序共有#{solds[uid]}次购买记录，您的使用反馈将帮助我们在产品和服务方面做得更多更好，问卷完成后您将获赠3000积分。如果您选择参加共享脱敏数据，可额外再获赠2000积分。感谢您的信任和支持，和我们一起为生命加油！"

        summary = '用户尚未完成问卷'
        comment = "完成可获3000~5000积分奖励"
        history = "#{Time.now.to_s[0..15]} 系统发放问卷"
        sql = "insert into tutem_mall.eb_follow_up ( template_id,uid,name,status,create_time,submit_time, content,summary,comment,history) values  ( #{template_id},#{uid},'#{name}',#{status},#{create_time},#{submit_time}, '#{@rds.escape content}','#{@rds.escape summary}', '#{@rds.escape comment}', '#{@rds.escape history}');"
        sqls += [sql]

        print '.'

    end

    commitTrans sqls
    puts "\n#{uids.size} done"

end

def initiate_survey start_day, end_day, template_id, dead_line, code = 'MANUAL'

    start_time = Time.parse(start_day + ' 00:00:00').to_i
    end_time = Time.parse(end_day + ' 23:59:59').to_i

    puts start_time
    puts end_time

    puts "new survey"

    sql = "select o.uid as uid, o.user_phone as order_phone, u.phone as user_phone, o.real_name as real_name, count(o.id) as sold, w.nickname as nickname from tutem_mall.eb_store_order o left join tutem_mall.eb_wechat_user w on o.uid = w.uid left join tutem_mall.eb_user u on o.uid = u.uid where o.paid = 1 and o.pay_time >= #{start_time} and o.pay_time <= #{end_time} group by u.uid";
    res = queryRds sql
    uids = [1]
    names = {}
    solds = {}
    phones = {}

    names.store 1, '董学锋'
    solds.store 1, 0
    dong_phones = ['13600060044']
    phones.store 1, dong_phones

    res.each do |r| 
        uid = r['uid']
        uids += [uid]
        names.store uid, r['nickname']
        names.store uid, r['real_name'] if r['nickname'] == '微信用户'
        solds.store uid, r['sold']

        tels = phones[uid].nil? ? [] : phones[uid]
        tels += [r['user_phone'].to_s] if r['user_phone'].to_s != ''
        tels += [r['order_phone'].to_s] if r['order_phone'].to_s != ''
        tels = tels.uniq
        phones.store uid, tels

    end

    sqls = []
    uids.each do |uid|

        #next if uid == 1 #TBO

        short_name = names[uid][0..16] #name只有64byte 
        name = short_name + "的随访问卷 #{code}-S#{template_id}"
        status = 0
        create_time = Time.now.to_i
        submit_time = 0

        #这个content要配合template_id对应的模板一起使用
        content = "尊敬的#{names[uid]}：您在#{start_day}~#{end_day}期间在小程序共有#{solds[uid]}次购买记录，我们希望根据您的反馈和需要，在产品和服务方面为您和其他用户做得更多更好。#{dead_line}前完成问卷您将获赠2999积分。如果您选择可公开共享脱敏数据，可额外再获赠1000积分。感谢您的信任和支持，我们愿与您为追求高质量的生命状态而共同努力！"

        summary = '用户尚未完成问卷'
        comment = "#{dead_line}前完成即可获2999~3999积分"
        history = "#{Time.now.to_s[0..15]} 系统发放问卷"

        sql = "insert into tutem_mall.eb_follow_up ( template_id,uid,name,phones,status,create_time,submit_time, content,summary,comment,history ) values  ( #{template_id},#{uid},'#{name}','#{@rds.escape phones[uid].to_json}', #{status},#{create_time},#{submit_time}, '#{@rds.escape content}','#{@rds.escape summary}', '#{@rds.escape comment}', '#{@rds.escape history}');"
        #sql = "update tutem_mall.eb_follow_up set phones = '#{@rds.escape phones[uid].to_json}' where template_id = #{template_id} and uid = #{uid}"

        sqls += [sql]
        print '.'

    end

    commitTrans sqls
    puts "\n#{uids.size} done"

end

def update_follow_up
    sql = 'select id, content from tutem_mall.eb_follow_up where status = 0 and template_id = 2';
    res = queryRds sql
    usqls = []
    res.each do |r|
        id = r['id']
        content = r['content']
        content = content.gsub('9月5日前完成','9月6日前完成');
        usql = "update tutem_mall.eb_follow_up set content = '#{@rds.escape content}' where id = #{id};"
        usqls += [usql]
    end
    commitTrans usqls
end

def export_follow_up
    headers = ['uid','name','summary','pics'];
    names = ['uid','name','summary','pics'];
    fid = 1
    items = []
    sql = "select f.id as id, f.answers as answers, f.uid as uid, u.real_name as name, f.summary from tutem_mall.eb_follow_up f left join tutem_mall.eb_user u on f.uid = u.uid  where f.status = 1 and f.template_id = #{fid} order by f.submit_time desc"
    res = queryRds sql
    res.each do |r|
        id =  r['id']
        janswers = r['answers']
        janswers = janswers.gsub("\n","|") #保存时可能未处理换行符
        answers = JSON.parse janswers
        pics = answers['pics'].join(',')
        digest = {
            "uid" => r['uid'],
            "name" => r['name'],
            "summary" => r['summary'],
            "pics" => pics
        }
        items += [digest]
    end
    save_to_excel headers,names,items,"followup-summary-#{fid}.xls"
end

def create_sms_event code = '2022Q1', template_id = 4, dead_line = '4月8日前'

    event = "FU-#{code}-S#{template_id}"

    sql = "select id, uid from tutem_mall.eb_store_order where uid in (select uid from tutem_mall.eb_follow_up where name like '%#{code}-S#{template_id}%')"
    oids = {} #按uid储存的oid
    (queryRds(sql)).each { |q| oids.store q['uid'], q['id'] }


    recs = {} #按手机储存的短信纪录
    uids = {} #按手机储存的uid纪录
    fids = {} #按手机储存的fid纪录

    sql = "select id, uid, phones from tutem_mall.eb_follow_up where name like '%#{code}-S#{template_id}%'"
    (queryRds(sql)).each do |q|
        fid = q['id']
        uid = q['uid']
        phones = JSON.parse q['phones']
        phones.each do |phone|
          data = {}
          data.store 'admin_name', '来自'
          data.store 'order_id', "#{oids[uid]}，#{dead_line}完成一季度订单随访即得29~39元积分礼券，点击 https://wxaurl.cn/oKyHfNaK36v 进官方小程序【我的随访】领取"
          recs.store phone, data
          uids.store phone, uid
          fids.store phone, fid
        end
    end

    usqls = []
    recs.each do |phone, data|
        uid = uids[phone]
        fid = fids[phone]
        order_id = oids[uid]
        #puts "#{sprintf '%06d', uid} #{sprintf '%06d',order_id} #{code} #{phone}"
        usql = "INSERT INTO tutem_mall.eb_sms_event (`uid`, `source_name`, `source_id`, `name`, `phone`, `template`, `data`, `create_time`, `event`) VALUES 
            (#{uid}, 'tutem_mall.eb_follow_up', #{fid}, '#{code}随访问奖励领取', '#{phone}', 'ORDER_PENDING_NOTICE', '#{data.to_json}', #{Time.now.to_i}, '#{event}');"
        usqls += [usql]
    end
    commitTrans usqls
    puts "#{usqls.size} sms event prepared"
    puts "NEXT STEP>>>> https://mall.totembio.cn/api/einv?event=#{event}"

end

#export_follow_up

#update_follow_up
#clear_follow_up
#initiate_dashu

#initiate_survey '2022-01-01','2022-03-31', 4, '4月6日', '2022Q1'
#create_sms_event '2022Q1', 4, '4月10日前'
